Just a quick blog post to highlight a change the is required when querying Project Online / SharePoint Online REST APIs in code when using the WebRequest class. Previously the PowerShell code sample below would work and authenticate with no issues:
#add SharePoint Online DLL - update the location if required $programFiles = [environment]::getfolderpath("programfiles") add-type -Path $programFiles'\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.dll' #set the environment details $PWAInstanceURL = "https://PWAURL" $username = "username" $password = "password" $securePass = ConvertTo-SecureString $password -AsPlainText -Force #set the Odata URL with the correct project fields needed, $url = $PWAInstanceURL + "/_api/ProjectData/Projects()?`$Filter=ProjectType ne 7&`$Select=ProjectId,ProjectName,ProjectPercentCompleted,ProjectOwnerName" #get all of the data from the OData URL [Microsoft.SharePoint.Client.SharePointOnlineCredentials]$spocreds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePass) $webrequest = [System.Net.WebRequest]::Create($url) $webrequest.Credentials = $spocreds $webrequest.Accept = "application/json;odata=verbose" $webrequest.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f") $response = $webrequest.GetResponse() $reader = New-Object System.IO.StreamReader $response.GetResponseStream() $data = $reader.ReadToEnd() $results = ConvertFrom-Json -InputObject $data $results.d.results
There has been a change in Office365 and this would now generate a 401 unauthorized error as seen below:
It is now required to use the authentication cookie, not sure if this is a permanent change or a temporary issue. Adding the line below resolves the issue:
$webrequest.Headers["Cookie"] = $spocreds.GetAuthenticationCookie($url)
#get all of the data from the OData URL [Microsoft.SharePoint.Client.SharePointOnlineCredentials]$spocreds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePass) $webrequest = [System.Net.WebRequest]::Create($url) $webrequest.Credentials = $spocreds $webrequest.Accept = "application/json;odata=verbose" $webrequest.Headers["Cookie"] = $spocreds.GetAuthenticationCookie($url) $webrequest.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f") $response = $webrequest.GetResponse()
This change would be applicable to all of my PowerShell code samples that query the Project Online OData API found here: https://gallery.technet.microsoft.com/site/search?f%5B0%5D.Type=User&f%5B0%5D.Value=PWMather&sortBy=Date
Hope that helps
How would you apply this to Power BI connecting to Project Online using Power Query?
It doesn’t apply to Power BI – this only applies when connecting to Project Online using the OData API from code such as PowerShell.